﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace _02._07.DataAccessWithAdoNet.Services
{
    public static class NorthwindDAL
    {
        private const string ConnectionString = @"Server=.\SQLEXPRESS ; Database=Northwind ; Integrated Security=true;";

        public static List<string> GetCategoryNames()
        {
            string ColumnCategoryName = "CategoryName";
            string sqlCommand = "SELECT DISTINCT " + ColumnCategoryName + " FROM Categories";

            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand(sqlCommand, connection);

            connection.Open();
            using (connection)
            {                
                SqlDataReader reader = command.ExecuteReader();
                List<string> categoryNames = new List<string>();

                while (reader.Read())
                {
                    string categoryName = (string)(reader[ColumnCategoryName]);
                    categoryNames.Add(categoryName);
                }

                return categoryNames;
            }
        }

        public static List<KeyValuePair<string, string>> GetProductNamesForEachCategoryName()
        {
            const string ColumnCategoryName = "CategoryName";
            const string ColumnProductName = "ProductName";
            const string ColumnCategoryID = "CategoryID";

            string sqlCommandText = "SELECT c." + ColumnCategoryName + ", p." + ColumnProductName + " " +
                "FROM Categories c" + " " +
                "JOIN Products p ON c." + ColumnCategoryID + "= p." + ColumnCategoryID + " " +
                "ORDER BY c." + ColumnCategoryName + ", p." + ColumnProductName;

            //sqlCommandText = "SELECT c.CategoryName, p.ProductName " + 
            //    "FROM Categories c " + 
            //    "JOIN Products p ON c.CategoryID = p.CategoryID " + 
            //    "ORDER BY c.CategoryName, p.ProductName";

            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand(sqlCommandText, connection);

            connection.Open();
            using (connection)
            {                
                SqlDataReader reader = command.ExecuteReader();
                var categoryNamesAndProductNames = new List<KeyValuePair<string,string>>();

                while (reader.Read())
                {
                    string categoryName = (string)(reader[ColumnCategoryName]);
                    string productName = (string)(reader[ColumnProductName]);
                    categoryNamesAndProductNames.Add(new KeyValuePair<string, string>(categoryName, productName));
                }

                return categoryNamesAndProductNames;
            }
        }

        public static void AddProduct(string productName, 
            int supplierID, 
            int categoryID, 
            string quantityPerUnit, 
            double unitPrice = 0.0,
            int unitsInStock = 0, 
            int unitsOnOrder = 0, 
            int reorderLevel = 0, 
            bool discontinued = true)
        { 
            string commandText = "INSERT INTO Products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, " + 
                "UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) " + 
                "Values(@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, " + 
                "@UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)";

            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand(commandText, connection);

            command.Parameters.Add(new SqlParameter("@ProductName", productName));
            command.Parameters.Add(new SqlParameter("@SupplierID", supplierID));
            command.Parameters.Add(new SqlParameter("@CategoryID", categoryID));
            command.Parameters.Add(new SqlParameter("@QuantityPerUnit", quantityPerUnit));
            command.Parameters.Add(new SqlParameter("@UnitPrice", unitPrice));
            command.Parameters.Add(new SqlParameter("@UnitsInStock", unitsInStock));
            command.Parameters.Add(new SqlParameter("@UnitsOnOrder", unitsOnOrder));
            command.Parameters.Add(new SqlParameter("@ReorderLevel", reorderLevel));
            command.Parameters.Add(new SqlParameter("@Discontinued", discontinued));

            connection.Open();
            using (connection)
            {                
                int affectedRows = command.ExecuteNonQuery();
            }		
        }
    }
}
